/**
 * 
 */
package org.gov.vis.data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.data.dto.DistributionSemenDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.DU;
import org.gov.vis.util.Util;

/**
 * @author user
 *
 */
public class DistributionSemenDaoImpl implements DistributionSemenDao{

	/**
	 *Method called to either Add or Edit a DistributionSemen
	 */
	@Override
	public void addOrEditDistributionSemen(final DistributionSemenDto _distributionSemenDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			if(_distributionSemenDto.getDisSemenId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_DISSEMEN);
				pStmt.setString(1, _distributionSemenDto.getCode());
				pStmt.setString(2, DU.format(_distributionSemenDto.getDoDstrbut(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(3, _distributionSemenDto.getBreed());
				pStmt.setString(4, _distributionSemenDto.getBullNo());
				pStmt.setInt(5, Integer.valueOf(_distributionSemenDto.getNoStrawas()));
				pStmt.setString(6, _distributionSemenDto.getValue());
				pStmt.setString(7, _distributionSemenDto.getDiscord());
				pStmt.setLong(8, _distributionSemenDto.getDisSemenId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_DISSEMEN);
				pStmt.setString(1, _distributionSemenDto.getCode());
				pStmt.setString(2, DU.format(_distributionSemenDto.getDoDstrbut(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(3, _distributionSemenDto.getBreed());
				pStmt.setString(4, _distributionSemenDto.getBullNo());
				pStmt.setInt(5, Integer.valueOf(_distributionSemenDto.getNoStrawas()));
				pStmt.setString(6, _distributionSemenDto.getValue());
				pStmt.setString(7, _distributionSemenDto.getDiscord());
				
				
			}

			pStmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a DistributionSemen
	 */
	@Override
	public void deleteDistributionSemen(final long _distributionSemenId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_DISSEMEN);
			pStmt.setLong(1, _distributionSemenId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of DistributionSemens based on the passed DistributionSemen 
	 */
	@Override
	public List<DistributionSemenDto> getDistributionSemens(final DistributionSemenDto _distributionSemenDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<DistributionSemenDto> _distributionSemenList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_DISSEMEN);
			if(_distributionSemenDto.getDisSemenId() > 0){
				pStmt.setLong(1, _distributionSemenDto.getDisSemenId());
				pStmt.setLong(2, _distributionSemenDto.getDisSemenId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_distributionSemenDto.getCode() != null && !_distributionSemenDto.getCode().trim().equals("")){
				pStmt.setString(3, _distributionSemenDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_distributionSemenDto.getBreed() != null && !_distributionSemenDto.getBreed().trim().equals("")){
				pStmt.setString(4, _distributionSemenDto.getBreed());
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_distributionSemenDto.getBullNo() != null && !_distributionSemenDto.getBullNo().trim().equals("")){
				pStmt.setString(5, _distributionSemenDto.getBullNo());
			}
			else{
				pStmt.setString(5, "%");
			}
			rsSet = pStmt.executeQuery();
			
			_distributionSemenList = populateDistributionSemens(rsSet);
		} catch (Exception e) {
			
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _distributionSemenList;
	}//end getDistributionSemens


	/**
	 * Method called to populate the DistributionSemen records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<DistributionSemenDto> populateDistributionSemens(final ResultSet _rsSet)throws Exception{
		List<DistributionSemenDto> _distributionSemenList = null;
		DistributionSemenDto _distributionSemenDto = null;
		
		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_distributionSemenList = new ArrayList<DistributionSemenDto>();
			}
			//initialize the DistributionSemen
			_distributionSemenDto = new DistributionSemenDto();
			_distributionSemenDto.setBreed(_rsSet.getString(DBConstants.COL_DISTRIBUTON_SEMEN_BREED));
			_distributionSemenDto.setBullNo(_rsSet.getString(DBConstants.COL_DISTRIBUTON_SEMEN_BULLNO));
			_distributionSemenDto.setCode(_rsSet.getString(DBConstants.COL_DISTRIBUTON_SEMEN_CODE));
			_distributionSemenDto.setDiscord(_rsSet.getString(DBConstants.COL_DISTRIBUTON_SEMEN_DISCORD));
			_distributionSemenDto.setDisSemenId(_rsSet.getLong(DBConstants.COL_DISTRIBUTON_SEMEN_ID));
			_distributionSemenDto.setDoDstrbut(_rsSet.getDate(DBConstants.COL_DISTRIBUTON_SEMEN_DODSTRB));
			_distributionSemenDto.setNoStrawas(_rsSet.getString(DBConstants.COL_DISTRIBUTON_SEMEN_NoSTRAWS));
			_distributionSemenDto.setValue(_rsSet.getString(DBConstants.COL_DISTRIBUTON_SEMEN_VALUE));
			
			//add the current Object to the list
			_distributionSemenList.add(_distributionSemenDto);
			
		}
		return _distributionSemenList;
	}//end populateDistributionSemens

	/**
	 * Method is called to search the DistributionSemens based on the distributionSemen.
	 */
	@Override
	public List<DistributionSemenDto> searchDistributionSemens(final DistributionSemenDto _distributionSemenDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<DistributionSemenDto> _distributionSemenList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_DISSEMEN);
			if(_distributionSemenDto.getDisSemenId() > 0){
				pStmt.setLong(1, _distributionSemenDto.getDisSemenId());
				pStmt.setLong(2, _distributionSemenDto.getDisSemenId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_distributionSemenDto.getCode() != null && !_distributionSemenDto.getCode().trim().equals("")){
				pStmt.setString(3, _distributionSemenDto.getCode() + "%");
			}
			else{
				pStmt.setString(3, "%");
			}
			if(_distributionSemenDto.getBreed() != null && !_distributionSemenDto.getBreed().trim().equals("")){
				pStmt.setString(4, _distributionSemenDto.getBreed() + "%");
			}
			else{
				pStmt.setString(4, "%");
			}
			if(_distributionSemenDto.getBullNo() != null && !_distributionSemenDto.getBullNo().trim().equals("")){
				pStmt.setString(5, _distributionSemenDto.getBullNo()+ "%");
			}
			else{
				pStmt.setString(5, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_distributionSemenList = populateDistributionSemens(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _distributionSemenList;
	}//end searchDistributionSemens

}
